﻿#region - Class details -
/*  
 *  Developer   : Wicus Botha
 *  Description : 
 *  History     : WB - 2008-10-20 - Created
 */
#endregion

using System;
using System.Collections.Generic;
using System.Data; 
using System.Text;
using System.Linq;
using IQ.DbBase;

namespace SqlServerAdmin
{
    public class SqlMdIndex : SqlMetaData
    {
        #region # Constructor #
        public SqlMdIndex() : base()
        {
        }
        #endregion

        #region - Private variables -
        private const string split = " -> ";
        private DataTable dtServerIndexes;
        private DataTable dtServerIndexFields;
        #endregion

        #region - Private methods -
        private string getIndexFieldFilter(string tableId, string indexId)
        {
            StringBuilder sb = new StringBuilder(SqlQueries.Field_TableId);
            sb.Append("=");
            sb.Append(tableId);
            sb.Append(" AND ");
            sb.Append(SqlQueries.Field_IndexId);
            sb.Append("=");
            sb.Append(indexId);
            return sb.ToString();
        }
        private DataRow[] getIndexFieldsServer(DataRow drTI) // Table & Index Row
        {
            return dtServerIndexFields.Select(getIndexFieldFilter(drTI[SqlQueries.Field_TableId].ToString(),
                drTI[SqlQueries.Field_IndexId].ToString()));
        }
        private DataRow[] getIndexFieldsFile(DataRow drTI) // Table & Index Row
        {
            return SqlMdFileData.Instance.FileIndexFields.Select(getIndexFieldFilter(drTI[SqlQueries.Field_TableId].ToString(),
                drTI[SqlQueries.Field_IndexId].ToString()));
        }
        private void createTempIndexFields()
        {
            tmpFields = new DataTable();
            DbHelper.AddDataColumn(tmpFields, SqlQueries.Field_IndexKeyNo, "System.Int32", true, 0);
            DbHelper.AddDataColumn(tmpFields, SqlQueries.Field_ColumnName, "System.String", true, "");
            DbHelper.AddDataColumn(tmpFields, "CompareIt", "System.Byte", true, 0);
            DbHelper.AddKeyToTable(tmpFields, "Name");
        }
        private void addTempFieldsRow(DataRow drSource, FieldChangeType change)
        {
            DataRow newRow = tmpFields.NewRow();
            newRow[SqlQueries.Field_IndexKeyNo] = drSource[SqlQueries.Field_IndexKeyNo];
            newRow[SqlQueries.Field_ColumnName] = drSource[SqlQueries.Field_ColumnName];
            newRow["CompareIt"] = (byte)change;
            tmpFields.Rows.Add(newRow);
        }
        private void copyFileFieldsToTemp(DataRow[] fields)
        {
            if (tmpFields == null)
                createTempIndexFields();
            //
            tmpFields.Rows.Clear();
            foreach (DataRow dr in fields)
            {
                addTempFieldsRow(dr, FieldChangeType.NoChange);
                //tmpFields.AcceptChanges();
            }
        }
        private DataRow getServerField(List<DataRow> server, string fieldName)
        { 
            DataRow dr = null;
            for (int i = 0; i < server.Count; ++i)
            {
                if (server[i][SqlQueries.Field_ColumnName].ToString() == fieldName)
                {
                    dr = server[i];
                    i = server.Count + 1;
                }
            }
            return dr;
        }
        private bool checkForDeleteFields(List<DataRow> server)
        {
            foreach (DataRow dr in server)
            {
                if (!checkExclusions(dr[SqlQueries.Field_TableName].ToString()))
                    addTempFieldsRow(dr, FieldChangeType.Deleted);
            }
            return server.Count > 0;
        }
        #endregion

        //#region - Protected variables -
        //#endregion

        #region - Protected methods -
        protected override void loadServerData()
        {
            dtServerIndexes = DbHelper.Load_Table(SqlQueries.GetSqlMdTableKeys());
            dtServerIndexFields = DbHelper.Load_Table(SqlQueries.GetSqlMdTableKeysFields());
        }
        #endregion

        #region - Public properties / variables -
        public DataTable FileIndexes
        {
            get { return SqlMdFileData.Instance.FileIndexes; }
        }
        public DataTable FileIndexFields
        {
            get { return SqlMdFileData.Instance.FileIndexFields; }
        }
        public DataTable ServerIndexes
        {
            get { return dtServerIndexes; }
        }
        public DataTable ServerIndexFields
        {
            get { return dtServerIndexFields; }
        }
        #endregion

        #region - Public methods -
        public bool CompareIndex(string nameTableIndex)
        {
            int splitPos = nameTableIndex.IndexOf(split);
            if (splitPos >= 0)
            {
                string table = nameTableIndex.Substring(0, splitPos);
                string index = nameTableIndex.Substring(splitPos + split.Length);
                return CompareIndex(table, index);
            }
            else
                return false;
        }
        public bool CompareIndex(string tableName, string indexName)
        {
            bool changed = false;
            // Get Server Table details
            DataRow drServer = dtServerIndexes.Select(getFilterFor(tableName, indexName, true)).FirstOrDefault();
            DataRow drFile = SqlMdFileData.Instance.FileIndexes.Select(getFilterFor(tableName, indexName, true)).FirstOrDefault();
            if (drServer != null && drFile != null)
            {
                // Check if index statuses are equal
                if (Convert.ToInt32(drServer[SqlQueries.Field_IndexStatus]) != Convert.ToInt32(drFile[SqlQueries.Field_IndexStatus]))
                {
                    changed = true;
                    addToChanges(tableName + split + indexName, ChangeType.IndexStatus);
                }
                else
                {
                    // Get fields for indexes
                    List<DataRow> drServerFields = getIndexFieldsServer(drServer).ToList();
                    DataRow[] drFileFields = getIndexFieldsFile(drFile);
                    if (drServerFields.Count != drFileFields.Length)
                    {
                        addToChanges(tableName + split + indexName, ChangeType.Changed);
                    }
                    else
                    {
                        //Compare index fields
                        copyFileFieldsToTemp(drFileFields);
                        for (int i = 0; i < tmpFields.Rows.Count; ++i)
                        {
                            // Get Server field name  
                            DataRow serverField = getServerField(drServerFields, tmpFields.Rows[i][SqlQueries.Field_ColumnName].ToString());
                            //
                            if (serverField != null)
                            {
                                if (serverField[SqlQueries.Field_IndexKeyNo].ToString() != tmpFields.Rows[i][SqlQueries.Field_IndexKeyNo].ToString())
                                {
                                    tmpFields.Rows[i]["CompareIt"] = (byte)FieldChangeType.OrderChange;
                                    changed = true;
                                }
                                drServerFields.Remove(serverField);
                            }
                            else
                            {
                                tmpFields.Rows[i]["CompareIt"] = (byte)FieldChangeType.NewField;
                                changed = true;
                            }
                        }
                        //
                        bool check = checkForDeleteFields(drServerFields);
                        if (changed || check)
                            addToChanges(tableName + split + indexName, ChangeType.Changed);
                    }
                }
            }
            else
            {
                if (drServer != null)
                {
                    copyFileFieldsToTemp(getIndexFieldsServer(drServer));
                    if (drFile == null)
                        addToChanges(tableName + split + indexName, ChangeType.NotExistInFile);
                }
                else if (drFile != null)
                {
                    copyFileFieldsToTemp(getIndexFieldsFile(drFile));
                    if (drServer == null)
                        addToChanges(tableName + split + indexName, ChangeType.NotExistOnServer);
                }
            }
            return changed;
        }

        public void CompareAllTableIndexes()
        {
            dtTemp = dtServerIndexes.Copy();
            dtChanges.Rows.Clear();
            DataTable fileData = SqlMdFileData.Instance.FileIndexes;
            if (fileData != null && fileData.Rows.Count > 0)
            {
                setMaxPosition(fileData.Rows.Count - 1);
                setCurrentPosition(0);
                for (int i = 0; i < fileData.Rows.Count; ++i)
                {
                    if (!checkExclusions(fileData.Rows[i][SqlQueries.Field_TableName].ToString()))
                    {
                        CompareIndex(fileData.Rows[i][SqlQueries.Field_TableName].ToString(),
                            fileData.Rows[i][SqlQueries.Field_IndexName].ToString());
                        deleteFromTemp(fileData.Rows[i][SqlQueries.Field_TableName].ToString(),
                            fileData.Rows[i][SqlQueries.Field_IndexName].ToString());
                    }
                    else
                        deleteFromTemp(fileData.Rows[i][SqlQueries.Field_TableName].ToString(), "");
                    setCurrentPosition(i);
                }
            }
            // Add Server left overs
            addTempToChanges();
        }
        public bool CreateIndex(string nameTableIndex)
        {
            bool result = false;
            int splitPos = nameTableIndex.IndexOf(split);
            if (splitPos >= 0)
            {
                string table = nameTableIndex.Substring(0, splitPos);
                string index = nameTableIndex.Substring(splitPos + split.Length);
                DataRow drFile = SqlMdFileData.Instance.FileIndexes.Select(getFilterFor(table, index, true)).FirstOrDefault();
                if (drFile != null)
                {
                    StringBuilder sb = new StringBuilder();
                    switch (Convert.ToInt32(drFile[SqlQueries.Field_IndexStatus]))
                    {
                        case 0: sb.Append("CREATE NONCLUSTERED INDEX "); break;
                        case 2: sb.Append("CREATE UNIQUE NONCLUSTERED INDEX "); break;
                        case 16: sb.Append("CREATE CLUSTERED INDEX "); break;
                        case 18: sb.Append("CREATE UNIQUE CLUSTERED INDEX "); break;
                    }
                    if (sb.Length == 0)
                        return false;
                    else
                    {
                        sb.Append("[" + index + "]");
                        sb.Append(" ON [dbo].");
                        sb.Append("[" + table + "]");
                        sb.Append("(");
                        DataRow[] drFileFields = getIndexFieldsFile(drFile);
                        for (int i = 0; i < drFileFields.Length; ++i)
                        {
                            sb.Append("[" + drFileFields[i][SqlQueries.Field_ColumnName] + "] ASC");
                            if (i < drFileFields.Length - 1)
                                sb.Append(",");
                        }
                        sb.Append(")");
                    }
                    try
                    {
                        DbHelper.ExecuteSql(sb.ToString());
                        result = true;
                    }
                    catch
                    {
                    }
                }
            }
            return result;
        }
        public bool DeleteIndex(string nameTableIndex)
        {
            bool result = false;
            int splitPos = nameTableIndex.IndexOf(split);
            if (splitPos >= 0)
            {
                string table = nameTableIndex.Substring(0, splitPos);
                string index = nameTableIndex.Substring(splitPos + split.Length);
                string tmpSql = "DROP INDEX [" + index + "] ON [dbo].[" + table + "]";
                try
                {
                    DbHelper.ExecuteSql(tmpSql);
                    result = true;
                }
                catch
                {
                }
            }
            return result;
        }
        #endregion
    }
}
